﻿using SQLite;
using System.Collections;

namespace ClassLibrary_fqy_NET.SQLiteHelper
{
    /// <summary>
    /// sqlite-net-pcl操作类
    /// 更轻量级、简化版
    /// 专门设计用于Xamarin和.NET平台
    /// </summary>
    public class SQLiteNETHelper
    {
        private CreateTableResult Result;
        private CreateTablesResult? tablesResult;
        private readonly SQLiteConnection? Connection;
        private readonly SQLiteAsyncConnection? asyncConnection;

        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="connectstring">数据库名称</param>
        public SQLiteNETHelper(string connectstring)
        {
            var strConn = new SQLiteConnectionString(connectstring);
            Connection = new SQLiteConnection(strConn);
            asyncConnection = new SQLiteAsyncConnection(strConn);
        }


        #region 异步方法
        /// <summary>
        /// 创建表(异步)
        /// </summary>
        /// <typeparam name="T">表名</typeparam>
        /// <returns></returns>
        public async Task<int> AsyncCreateTable<T>() where T : new()
        {
            if (asyncConnection != null)
            {
                Result = await asyncConnection.CreateTableAsync<T>();
            }
            return (int)Result;
        }
        /// <summary>
        /// 创建表(异步)
        /// </summary>
        /// <typeparam name="T">表名1</typeparam>
        /// <typeparam name="T1">表名2</typeparam>
        /// <returns></returns>
        public async Task<CreateTablesResult?> AsyncCreateTable<T, T1>() where T : new() where T1 : new()
        {
            if (asyncConnection != null)
            {
                tablesResult = await asyncConnection.CreateTablesAsync<T, T1>();
            }
            return tablesResult;
        }
        /// <summary>
        /// 创建表(异步)
        /// </summary>
        /// <typeparam name="T">表名1</typeparam>
        /// <typeparam name="T1">表名2</typeparam>
        /// <typeparam name="T2">表名3</typeparam>
        /// <returns></returns>
        public async Task<CreateTablesResult?> AsyncCreateTable<T, T1, T2>() where T : new() where T1 : new() where T2 : new()
        {
            if (asyncConnection != null)
            {
                tablesResult = await asyncConnection.CreateTablesAsync<T, T1, T2>();
            }
            return tablesResult;
        }
        /// <summary>
        /// 创建表(异步)
        /// </summary>
        /// <typeparam name="T">表名1</typeparam>
        /// <typeparam name="T1">表名2</typeparam>
        /// <typeparam name="T2">表名3</typeparam>
        /// <typeparam name="T3">表名4</typeparam>
        /// <returns></returns>
        public async Task<CreateTablesResult?> AsyncCreateTable<T, T1, T2, T3>() where T : new() where T1 : new() where T2 : new() where T3 : new()
        {
            if (asyncConnection != null)
            {
                tablesResult = await asyncConnection.CreateTablesAsync<T, T1, T2, T3>();
            }
            return tablesResult;
        }
        /// <summary>
        /// 创建表(异步)
        /// </summary>
        /// <typeparam name="T">表名1</typeparam>
        /// <typeparam name="T1">表名2</typeparam>
        /// <typeparam name="T2">表名3</typeparam>
        /// <typeparam name="T3">表名4</typeparam>
        /// <typeparam name="T4">表名5</typeparam>
        /// <returns></returns>
        public async Task<CreateTablesResult?> AsyncCreateTable<T, T1, T2, T3, T4>() where T : new() where T1 : new() where T2 : new() where T3 : new() where T4 : new()
        {
            if (asyncConnection != null)
            {
                tablesResult = await asyncConnection.CreateTablesAsync<T, T1, T2, T3, T4>();
            }
            return tablesResult;
        }
        /// <summary>
        /// 插入数据(异步)
        /// </summary>
        /// <param name="obj">要插入的数据</param>
        /// <returns></returns>
        public async Task<int> AsyncInsertData(object obj)
        {
            if (asyncConnection != null)
            {
                int result = await asyncConnection.InsertOrReplaceAsync(obj);
                return result;
            }
            return 0;
        }
        /// <summary>
        /// 插入多组数据(异步)
        /// </summary>
        /// <param name="obj">要插入的数据集合</param>
        /// <returns></returns>
        public async Task<int> AsyncInsertData(IEnumerable obj)
        {
            if (asyncConnection != null)
            {
                int result = await asyncConnection.InsertAllAsync(obj);
                return result;
            }
            return 0;
        }
        /// <summary>
        /// 更新数据(异步)
        /// </summary>
        /// <param name="obj">要更新的对象。它必须具有使用PrimaryKeyAttribute指定的主键</param>
        /// <returns></returns>
        public async Task<int> AsyncUpdateData(object obj)
        {
            if (asyncConnection != null)
            {
                int result = await asyncConnection.UpdateAsync(obj);
                return result;
            }
            return 0;
        }
        /// <summary>
        /// 获取选定的表中的所有数据(异步)
        /// </summary>
        /// <typeparam name="T">表名</typeparam>
        /// <returns></returns>
        public async Task<List<T>> AsyncGetAllData<T>() where T : new()
        {
            if (asyncConnection != null)
            {
                List<T> result = await asyncConnection.Table<T>().ToListAsync();
                return result;
            }
            return new List<T>();
        }
        /// <summary>
        /// 获取选定的表中的指定具有主键属性的项的单条数据(异步)
        /// </summary>
        /// <typeparam name="T">表名</typeparam>
        /// <param name="obj">主键名</param>
        /// <returns></returns>
        public async Task<T?> AsyncGetData<T>(object obj) where T : new()
        {
            if (asyncConnection != null)
            {
                T result = await asyncConnection.GetAsync<T>(obj);
                return result;
            }
            return default;
        }
        /// <summary>
        /// 从指定的表中删除所有对象(异步)
        /// </summary>
        /// <typeparam name="T">指定表名</typeparam>
        /// <returns></returns>
        public async Task<int> AsyncDeleteAll<T>() where T : new()
        {
            if (asyncConnection != null)
            {
                int result = await asyncConnection.DeleteAllAsync<T>();
                return result;
            }
            return 0;
        }
        /// <summary>
        /// 在给定带有参数的命令文本（SQL）的情况下创建SQLiteCommand。放置“？”在每个参数的命令文本中，然后执行该命令(异步)
        /// </summary>
        /// <typeparam name="T">表名</typeparam>
        /// <param name="query">完全转义的SQL</param>
        /// <param name="obj">用于替换在查询中出现的参数“？”</param>
        /// <returns></returns>
        public async Task<List<T>?> AsyncQuery<T>(string query, params object[] obj) where T : new()
        {
            if (asyncConnection != null)
            {
                List<T> result = await asyncConnection.QueryAsync<T>(query, obj);
                return result;
            }
            return default;
        }
        /// <summary>
        /// 在给定带有参数的命令文本（SQL）的情况下创建SQLiteCommand。放置“？”在每个参数的命令文本中，然后执行该命令(异步)
        /// </summary>
        /// <param name="query">完全转义的SQL</param>
        /// <param name="obj">用于替换在查询中出现的参数“？”</param>
        /// <returns></returns>
        public async Task<int> AsyncExecute(string query, params object[] obj)
        {
            if (asyncConnection != null)
            {
                int result = await asyncConnection.ExecuteAsync(query, obj);
                return result;
            }
            return 0;
        }

        #endregion

        #region 同步方法
        /// <summary>
        /// 创建表
        /// </summary>
        /// <typeparam name="T">表名</typeparam>
        /// <returns></returns>
        public int CreateTable<T>()
        {
            if (Connection != null)
            {
                Result = Connection.CreateTable<T>();
            }
            return (int)Result;
        }
        /// <summary>
        /// 创建表
        /// </summary>
        /// <typeparam name="T">表名1</typeparam>
        /// <typeparam name="T1">表名2</typeparam>
        /// <returns></returns>
        public CreateTablesResult? CreateTable<T, T1>() where T : new() where T1 : new()
        {
            if (Connection != null)
            {
                tablesResult = Connection.CreateTables<T, T1>();
            }
            return tablesResult;
        }
        /// <summary>
        /// 创建表
        /// </summary>
        /// <typeparam name="T">表名1</typeparam>
        /// <typeparam name="T1">表名2</typeparam>
        /// <typeparam name="T2">表名3</typeparam>
        /// <returns></returns>
        public CreateTablesResult? CreateTable<T, T1, T2>() where T : new() where T1 : new() where T2 : new()
        {
            if (Connection != null)
            {
                tablesResult = Connection.CreateTables<T, T1, T2>();
            }
            return tablesResult;
        }
        /// <summary>
        /// 创建表
        /// </summary>
        /// <typeparam name="T">表名1</typeparam>
        /// <typeparam name="T1">表名2</typeparam>
        /// <typeparam name="T2">表名3</typeparam>
        /// <typeparam name="T3">表名4</typeparam>
        /// <returns></returns>
        public CreateTablesResult? CreateTable<T, T1, T2, T3>() where T : new() where T1 : new() where T2 : new() where T3 : new()
        {
            if (Connection != null)
            {
                tablesResult = Connection.CreateTables<T, T1, T2, T3>();
            }
            return tablesResult;
        }
        /// <summary>
        /// 创建表
        /// </summary>
        /// <typeparam name="T">表名1</typeparam>
        /// <typeparam name="T1">表名2</typeparam>
        /// <typeparam name="T2">表名3</typeparam>
        /// <typeparam name="T3">表名4</typeparam>
        /// <typeparam name="T4">表名5</typeparam>
        /// <returns></returns>
        public CreateTablesResult? CreateTable<T, T1, T2, T3, T4>() where T : new() where T1 : new() where T2 : new() where T3 : new() where T4 : new()
        {
            if (Connection != null)
            {
                tablesResult = Connection.CreateTables<T, T1, T2, T3, T4>();
            }
            return tablesResult;
        }
        /// <summary>
        /// 删除表
        /// </summary>
        /// <typeparam name="T">表名</typeparam>
        /// <returns></returns>
        public int DropTable<T>()
        {
            if (Connection != null)
            {
                int result = Connection.DropTable<T>();
                return result;
            }
            return 0;
        }
        /// <summary>
        /// 插入数据
        /// </summary>
        /// <param name="obj">要插入的数据</param>
        /// <returns></returns>
        public int InsertData(object obj)
        {
            if (Connection != null)
            {
                int result = Connection.InsertOrReplace(obj);
                return result;
            }
            return 0;
        }
        /// <summary>
        /// 插入多组数据
        /// </summary>
        /// <param name="obj">要插入的数据集合</param>
        /// <returns></returns>
        public int InsertData(IEnumerable obj)
        {
            if (Connection != null)
            {
                int result = Connection.InsertAll(obj);
                return result;
            }
            return 0;
        }
        /// <summary>
        /// 更新数据
        /// </summary>
        /// <param name="obj">要更新的对象。它必须具有使用PrimaryKeyAttribute指定的主键</param>
        /// <returns></returns>
        public int UpdateData(object obj)
        {
            if (Connection != null)
            {
                int result = Connection.Update(obj);
                return result;
            }
            return 0;
        }
        /// <summary>
        /// 获取选定的表中的所有数据
        /// </summary>
        /// <typeparam name="T">表名</typeparam>
        /// <returns></returns>
        public List<T> GetAllData<T>() where T : new()
        {
            if (Connection != null)
            {
                List<T> values = Connection.Table<T>().ToList();
                return values;
            }
            return new List<T>();
        }
        /// <summary>
        /// 获取选定的表中的指定具有主键属性的项的单条数据
        /// </summary>
        /// <typeparam name="T">表名</typeparam>
        /// <param name="obj">主键名</param>
        /// <returns></returns>
        public T? GetData<T>(object obj) where T : new()
        {
            if (Connection != null)
            {
                T result = Connection.Get<T>(obj);
                return result;
            }
            return default;
        }
        /// <summary>
        /// 从指定的表中删除所有对象
        /// </summary>
        /// <typeparam name="T">指定表名</typeparam>
        /// <returns>已删除的对象数</returns>
        public int DeleteAll<T>() where T : new()
        {
            if (Connection != null)
            {
                int result = Connection.DeleteAll<T>();
                return result;
            }
            return 0;
        }
        /// <summary>
        /// 在给定带有参数的命令文本（SQL）的情况下创建SQLiteCommand。放置“？”在每个参数的命令文本中，然后执行该命令。
        /// 它使用为给定类型自动生成的映射返回结果的每一行。
        /// </summary>
        /// <typeparam name="T">表名</typeparam>
        /// <param name="query">完全转义的SQL</param>
        /// <param name="obj">用于替换在查询中出现的参数“？”</param>
        /// <returns>一个枚举对象，查询返回的每一行都有一个结果。</returns>
        public List<T>? Query<T>(string query, params object[] obj) where T : new()
        {
            if (Connection != null)
            {
                List<T> result = Connection.Query<T>(query, obj);
                return result;
            }
            return default;
        }
        /// <summary>
        /// 在给定带有参数的命令文本（SQL）的情况下创建SQLiteCommand。放置“？”在每个参数的命令文本中，然后执行该命令。
        /// 如果不希望返回行，请使用此方法而不是Query。
        /// </summary>
        /// <param name="query">完全转义的SQL</param>
        /// <param name="obj">用于替换在查询中出现的参数“？”</param>
        /// <returns>由于执行此操作而在数据库中修改的行数</returns>
        public int Execute(string query, params object[] obj)
        {
            if (Connection != null)
            {
                int result = Connection.Execute(query, obj);
                return result;
            }
            return 0;
        }
        #endregion

    }
}
